Download Developing SQL Databases.70-762.Test4Prep.2018-07-12.68q.vcex

The Products table needs a primary key constraint on the ProductID field. The Orders table needs a foreign key constraint on the productID field, with a reference to the ProductID field in the Products table.

Using TRY...CATCH in Transact-SQL Errors in Transact-SQL code can be processed by using a TRY…CATCH construct. TRY…CATCH can use the following error function to capture error information:ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times. References:https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

B: We should use WITH NOCHECK asexisting records in the ProductReview table must not be validated with the Product table.C: Deletes should not be allowed, so we use ON DELETE NO ACTION.Updates should be allowed, so we use ON DELETE NO CASCADE NO ACTION: the Database Engine raises an error,and the update action on the row in the parent table is rolled back.CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }Specifieswhat action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifieswhat action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION. Note: You must modify the ProductReview Table to meet the following requirements:The table must reference the ProductID column in the Product table Existing records in the ProductReview table must not be validated with the Product table. Deleting records in the Product table must not be allowed if records are referencedby the ProductReview table. Changes to records in the Product table must propagate to the ProductReview table. References:https://msdn.microsoft.com/en-us/library/ms190273.aspxhttps://msdn.microsoft.com/en-us/library/ms188066.aspx

Requirements: You must modify the Orders table to meet the following requirements:Create new rows in the table without granting INSERT permissions to the table. Notify the sales person who places an order whether or not the order was completed. References:https://msdn.microsoft.com/en-us/library/ms186755.aspx

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. The output format for Trace Flag 1222 only returns information in an XML-like format. References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

Two tables is enough.CustomerID must be in both tables.

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:They can be data types not allowed as index key columns. They are not considered by the Database Engine when calculating the number of index key columns or index key size.